﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.OracleClient;
using System.Data;

using DQYT.HTDZ.Model;
using Maticsoft.DBUtility;


namespace DQYT.HTDZ.OracleDAL
{
    public class TB_Workflow_Log_DAL
    {
        /// <summary>
        /// 添加审核流水
        /// </summary>
        /// <param name="logModel"></param>
        public void AddWorkflowLog(TB_Workflow_Log_Model logModel)
        {
            string executeSql = @"INSERT INTO tb_workflow_log(ID, obj_id, use_name, use_code, sh_rmk, sh_op_name, sh_time, fromstatus, tostatus)
                                  VALUES(sk_id.nextval, :obj_id, :use_name, :use_code, :sh_rmk, :sh_op_name, SYSDATE, :fromstatus, :tostatus)";
            OracleParameter[] dbParams = new OracleParameter[7];
            dbParams[0] = new OracleParameter(":obj_id", logModel.ObjID);
            dbParams[1] = new OracleParameter(":use_name", logModel.USE_NAME);
            dbParams[2] = new OracleParameter(":use_code", logModel.USE_CODE);
            dbParams[3] = new OracleParameter(":sh_rmk", logModel.SH_RMK);
            dbParams[4] = new OracleParameter(":sh_op_name", logModel.SH_OP_NAME);
            dbParams[5] = new OracleParameter(":fromstatus", logModel.FromStatus);
            dbParams[6] = new OracleParameter(":tostatus", logModel.ToStatus);
            DbHelperOra.ExecuteSql(executeSql, dbParams);

            executeSql = "UPDATE tb_fa SET shenhe = :shenhe WHERE ID = :obj_id";
            OracleParameter[] updateParams = new OracleParameter[2];
            updateParams[0] = new OracleParameter(":shenhe", logModel.ToStatus);
            updateParams[1] = new OracleParameter(":obj_id", logModel.ObjID);
            DbHelperOra.ExecuteSql(executeSql, updateParams);
        }

        /// <summary>
        /// 取已办
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetDone(string userCode, int pageIndex, int pageSize, out int recordCount)
        {
            DataTable doneTable = null;

            string sql = string.Format(@"SELECT d.tmp_mc, c.jh, a.id, a.jing_id, a.sjr, a.sjrq, b.sh_time, b.sh_op_name, b.sh_rmk FROM tb_fa a
                                            JOIN tb_workflow_log b ON b.obj_id = a.id
                                            JOIN skgysjb c ON c.id = a.jing_id
                                            JOIN tb_template_menu d ON d.tmp_id = a.tmp_id
                                            WHERE b.use_code = '{0}'
                                            ORDER BY b.sh_time DESC", userCode);
            doneTable = DbHelperOra.ExecutePageQuery(sql, pageSize, pageIndex, out recordCount);
            return doneTable;
        }

        /// <summary>
        /// 初审复审取待办
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetDepartWait(string userCode, int pageIndex, int pageSize, out int recordCount)
        {
            DataTable waitTable = null;

            string sql = string.Format(@"SELECT a.sjr, a.sjrq, b.tmp_mc, c.jh, a.id, a.shenhe
                                          FROM TB_FA A
                                          JOIN tb_template_menu B ON b.tmp_id = a.tmp_id
                                          JOIN skgysjb c ON c.id = a.jing_id
                                         WHERE A.SJR_CODE IN
                                               (SELECT A.USE_CODE
                                                  FROM TB_SYS_USER A
                                                 WHERE A.BUM_ID =
                                                       (SELECT BUM_ID FROM TB_SYS_USER A WHERE A.USE_CODE = '{0}'))
                                               AND NOT Exists (SELECT 1 from tb_workflow_log b WHERE b.obj_id = a.id AND B.USE_CODE = '{0}')
                                               AND a.shenhe < 2 
                                               order by a.sjrq desc", userCode);

            waitTable = DbHelperOra.ExecutePageQuery(sql, pageSize, pageIndex, out recordCount);
            
            return waitTable;
        }

        /// <summary>
        /// 终审人员待办
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetLastWait(int pageIndex, int pageSize, out int recordCount)
        {
            DataTable waitTable = null;

            string sql = @"SELECT A.SJR, A.SJRQ, B.TMP_MC, C.JH, A.ID, A.SHENHE
                             FROM TB_FA A
                             JOIN TB_TEMPLATE_MENU B
                               ON B.TMP_ID = A.TMP_ID
                             JOIN SKGYSJB C
                               ON C.ID = A.JING_ID
                            WHERE A.SHENHE = 2";
            waitTable = DbHelperOra.ExecutePageQuery(sql, pageSize, pageIndex, out recordCount);
            return waitTable;
        }

        /// <summary>
        /// 取审核流水
        /// </summary>
        /// <param name="objId">井设计方案ID</param>
        /// <returns></returns>
        public DataTable GetWorkFowLog(string objId)
        {
            string sql = string.Format("select * from tb_workflow_log a where a.obj_id = {0}", objId);
            return DbHelperOra.Query_Table(sql);
        }
    }
}
